﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeBuilder
{
    class DatabaseLoader
    {
        public string ConnectionString { get; set; }

        public string Provider { get; set; }

        public List<Table> Tables { get; set; }

        public void Load()
        {
          var factory= DbProviderFactories.GetFactory(Provider);
            using (var conn = factory.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                var table = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables);
                Tables = new List<Table>();
                foreach (DataRow item in table.Rows)
                {
                    var t = new Table { Name = item[2].ToString() };
                    switch (Provider)
                    {
                        case "System.Data.SqlClient":
                            GetSQLServerColumns(conn,t);
                            break;
                        case "MySql.Data.MySqlClient":
                            GetMySQLColumns(conn, t);
                            break;
                        default:
                            break;
                    }
                    Tables.Add(t);
                }
            }
        }

        private void GetMySQLColumns(DbConnection conn, Table t)
        {
            using (var comm = conn.CreateCommand())
            {
                comm.CommandText = $"SELECT * from information_schema.`COLUMNS` WHERE TABLE_SCHEMA='{conn.Database}' and TABLE_NAME='{t.Name}'";
                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    t.Columns.Add(new Column
                    {
                        ColumnName = dr["COLUMN_NAME"].ToString(),
                        ColumnType = dr["DATA_TYPE"].ToString(),
                        CLRType = GetType(dr["DATA_TYPE"].ToString()),
                        DefaultValue = dr["COLUMN_DEFAULT"].ToString(),
                        Description = dr["COLUMN_COMMENT"].ToString(),
                        IsIdentity = (dr["EXTRA"].ToString() == "auto_increment"),
                        IsPrimary = (dr["COLUMN_KEY"].ToString() == "PRI"),
                        IsNullable = dr["IS_NULLABLE"].ToString() == "YES",
                        MaxLength = dr["CHARACTER_MAXIMUM_LENGTH"].ToString(),
                    });
                }
                dr.Close();
            }
        }
        private void GetSQLServerColumns(DbConnection conn, Table t)
        {
            using (var comm = conn.CreateCommand())
            {
                comm.CommandText = string.Format(@"SELECT C.name as ColumnName,T.name as ColumnType,convert(bit,C.IsNullable) as IsNullable  
     ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end) as IsPrimary  
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as IsIdentity,C.Length as Length,COLUMNPROPERTY(C.id,C.name,'PRECISION') as Precision
     ,ISNULL(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as Scale,ISNULL(CM.text,'') as DefaultValue,ISNULL(ETP.value,'') AS Description   
    FROM syscolumns C INNER JOIN systypes T ON C.xusertype = T.xusertype    
    left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'    
    left join syscomments CM on C.cdefault=CM.id WHERE C.id = object_id('{0}')", t.Name);
                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    t.Columns.Add(new Column
                    {
                        ColumnName = dr["ColumnName"].ToString(),
                        ColumnType = dr["ColumnType"].ToString(),
                        CLRType = GetType(dr["ColumnType"].ToString()),
                        IsNullable = (bool)dr["IsNullable"],
                        IsPrimary = (bool)dr["IsPrimary"],
                        IsIdentity = (bool)dr["IsIdentity"],
                        MaxLength = dr["Length"].ToString(),
                        Precision = dr["Precision"].ToString(),
                        Scale = (int)dr["Scale"],
                        DefaultValue = dr["DefaultValue"].ToString(),
                        Description = dr["Description"].ToString(),
                    });
                }
                dr.Close();
            }
        }

        private string GetType(string v)
        {
            switch (v)
            {
                case "bigint":
                    return "long";
                case "tinyint":
                    return "byte";
                case "bit":
                    return "bool";
                case "char":
                case "varchar":
                case "nchar":
                case "nvarchar":
                    return "string";
                case "datetime":
                case "date":
                case "time":
                    return "DateTime";
                case "uniqueidentifier":
                    return "Guid";
                default:
                    return v;
            }
        }
    }

    static class Extensions
    {
        public static string Singularize(this string value)
        {
            return System.Data.Entity.Design.PluralizationServices.PluralizationService.CreateService(new System.Globalization.CultureInfo("en")).Singularize(value);
        }
        public static string Pluralize(this string value)
        {
            return System.Data.Entity.Design.PluralizationServices.PluralizationService.CreateService(new System.Globalization.CultureInfo("en")).Pluralize(value);
        }

    }

    public class Table
    {
        public string Name { get; set; }

        public string Entity => Name.Singularize();

        public List<Column> Columns { get; set; } = new List<Column>();
    }

    public class Column
    {
        /// <summary>
        /// 列名
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 系统数据类型
        /// </summary>
        public string ColumnType { get; set; }
        /// <summary>
        /// 转换后数据类型
        /// </summary>
        public string CLRType { get; set; }
        /// <summary>
        /// 主键
        /// </summary>
        public bool IsPrimary { get; set; }
        /// <summary>
        /// 是否允许为空
        /// </summary>
        public bool IsNullable { get; set; }
        /// <summary>
        /// 自增
        /// </summary>
        public bool IsIdentity { get; set; }
        /// <summary>
        /// 最大长度
        /// </summary>
        public string MaxLength { get; set; }
        /// <summary>
        /// 如果列包含的是数值，则为该列的精度；否则为0
        /// </summary>
        public string Precision { get; set; }
        /// <summary>
        /// 如果列包含的是数值，则为列的小数位数；否则为0
        /// </summary>
        public int Scale { get; set; }
        /// <summary>
        /// 默认值
        /// </summary>
        public string DefaultValue { get; set; }
        /// <summary>
        /// 数据库备注
        /// </summary>
        public string Description { get; set; }

    }
}
